USE Sample
GO--d

CREATE TABLE Company
(
 Company_Id			NUMERIC(18,0)  IDENTITY(1,1) NOT NULL,
 Company_Name		VARCHAR(100)   NOT NULL,
 CONSTRAINT PK_Company PRIMARY KEY CLUSTERED
 (
  Company_Id
 )
)
GO


CREATE TABLE Product
(
 Product_Id			NUMERIC(18,0)	IDENTITY(1,1) NOT NULL,
 Product_Name		VARCHAR(100)	NOT NULL,
 Product_Rate		DECIMAL(18,0)   NOT NULL,
 Company_Id			NUMERIC(18,0)   NOT NULL
 CONSTRAINT PK_Product PRIMARY KEY CLUSTERED
 (
  Product_Id
 )
)
GO


ALTER TABLE Product ADD CONSTRAINT FK_Product_Company FOREIGN KEY (Company_Id)
REFERENCES Company(Company_Id)
GO


CREATE TABLE PurMain
(
 PurMain_Id			NUMERIC(18,0)  IDENTITY(1,1) NOT NULL,
 PurMain_SlNo		NUMERIC(18,0)  NOT NULL,
 PurMain_Total		NUMERIC(18,0)  NOT NULL
 CONSTRAINT PK_PurMain PRIMARY KEY CLUSTERED
 (
  PurMain_Id
 )
)
GO



CREATE TABLE PurSub
(
 PurSub_Id			NUMERIC(18,0)  IDENTITY(1,1) NOT NULL,
 PurSub_Rate		DECIMAL(18,2)  NOT NULL,
 Product_Id			NUMERIC(18,0)  NOT NULL,
 PurMain_Id			NUMERIC(18,0)  NOT NULL,
 CONSTRAINT PK_PurSub PRIMARY KEY CLUSTERED
 (
  PurSub_Id
 )
)
GO


ALTER TABLE PurSub  ADD CONSTRAINT FK_PurSub_Product FOREIGN KEY (Product_Id)
REFERENCES Product(Product_Id)
GO


ALTER TABLE PurSub  ADD CONSTRAINT FK_PurSub_PurMain FOREIGN KEY (PurMain_Id)
REFERENCES PurMain(PurMain_Id)
GO

--Stored Procedure

CREATE PROCEDURE Company_DisplayTop100

AS SET NOCOUNT ON

BEGIN
 SELECT TOP 100
  Company.Company_Id,
  Company.Company_Name
 FROM
  Company
END
GO


CREATE PROCEDURE Company_SelectTop100
(
 @Company_Name			VARCHAR(100)
)
AS SET NOCOUNT ON

BEGIN
 SELECT TOP 100
  Company.Company_Id,
  Company.Company_Name
 FROM
  Company
 WHERE
  Company_Name			LIKE		@Company_Name
END
GO


CREATE PROCEDURE Company_InsertCompany
(
 @Company_Name			VARCHAR(100)
)
AS SET NOCOUNT ON

BEGIN
 INSERT INTO Company
 (
  Company_Name
 )
 VALUES
 (
  @Company_Name
 )
END
GO



CREATE PROCEDURE Company_UpdateCompany
(
 @Company_Id			NUMERIC,
 @Company_Name			VARCHAR(100)
)
AS SET NOCOUNT ON

BEGIN
 UPDATE
  Company
 SET
  Company_Name			=	  @Company_Name
 WHERE
  Company_Id			=	  @Company_Id 
END
GO
CREATE PROCEDURE Product_DisplayTop100

AS SET NOCOUNT ON

BEGIN
 SELECT TOP 100
  Product.Product_Id,
  Product.Product_Name
 FROM
  Product
END
GO


CREATE PROCEDURE Product_SelectTop100
(
 @Product_Name			VARCHAR(100)
)
AS SET NOCOUNT ON

BEGIN
 SELECT TOP 100
  Product.Product_Id,
  Product.Product_Name
 FROM
  Product
 WHERE
  Product_Name			LIKE		@Product_Name
END
GO


CREATE PROCEDURE Product_InsertProduct
(
 @Product_Name			VARCHAR(100),
 @Product_Rate			DECIMAL
)
AS SET NOCOUNT ON

BEGIN
 INSERT INTO Product
 (
  Product_Name,
  Product_Rate
 )
 VALUES
 (
  @Product_Name,
  @Product_Rate
 )
END
GO



CREATE PROCEDURE Product_UpdateProduct
(
 @Product_Id			NUMERIC,
 @Product_Name			VARCHAR(100),
  @Product_Rate			DECIMAL
)
AS SET NOCOUNT ON

BEGIN
 UPDATE
  Product
 SET
  Product_Name			=	  @Product_Name,
  Product_Rate			=	  @Product_Rate
 WHERE
  Product_Id			=	  @Product_Id 
END
GO